package com.example.score.mapper;

import com.baomidou.mybatisplus.core.mapper.Mapper;
import com.example.score.entity.QuestionScore;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface QuestionScoreMapper extends Mapper<QuestionScore> {
    int deleteByPrimaryKey(Long id);

    int insert(QuestionScore record);

    int insertSelective(QuestionScore record);

    QuestionScore selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(QuestionScore record);

    int updateByPrimaryKey(QuestionScore record);

    int updateBatch(@Param("list") List<QuestionScore> list);

    int batchInsert(@Param("list") List<QuestionScore> list);

    @Insert("<script>" +
            "INSERT INTO t_question_score (student_submit_id,student_answer,exam_detail_id, question_id, score, is_right, created_time, updated_time) VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.studentSubmitId}, #{item.studentAnswer}, #{item.examDetailId}, #{item.questionId}, #{item.score}, #{item.isRight}, now(), now())" +
            "</foreach>" +
            "</script>")
    void insertBatch(@Param("list") List<QuestionScore> questionScores);

    @Select("<script>" +
            "SELECT * FROM t_question_score WHERE student_submit_id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    List<QuestionScore> selectByStudentSubmitIds(@Param("ids") List<Long> studentSubmitIds);

    @Select("SELECT qs.*, qi.question_num, qi.is_subjective_item " +
            "FROM t_question_score qs " +
            "LEFT JOIN t_question_info qi ON qs.question_id = qi.question_id " +
            "WHERE qs.student_submit_id = #{submitId} " +
            "ORDER BY qi.question_num")
    List<QuestionScore> selectByStudentSubmitId(@Param("submitId") Long submitId);

    @Select("SELECT " +
            "qi.question_id as questionId," +
            "qi.question_num as questionNum, qi.question_bank_id as questionBankId, " +
            "qi.question_type_name as questionTypeName, ROUND(AVG(qs.score * 100)/ qi.question_score, 2) as scoreRate " +
            "FROM t_question_score qs JOIN t_question_info qi ON qs.question_id = qi.question_id " +
            "JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "WHERE ss.exam_detail_id = #{examDetailId} and ss.submit_status = 1 " +
            "GROUP BY qi.question_id,qi.question_num,question_type_name," +
            "qi.question_bank_id,qi.question_score ORDER BY scoreRate ASC;\n")
    List<Map<String, Object>> selectQuestionStats(
            @Param("examDetailId") Long examDetailId
    );

    @Select("SELECT " +
            "qi.question_id as questionId, ss.class_id as classId, " +
            "qi.question_num as questionNum, qi.question_alias as questionAlias, qi.question_bank_id as questionBankId, " +
            "qi.question_type_name as questionTypeName, ROUND(AVG(qs.score * 100)/ qi.question_score, 2) as scoreRate " +
            "FROM t_question_score qs JOIN t_question_info qi ON qs.question_id = qi.question_id " +
            "JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "WHERE ss.exam_detail_id = #{examDetailId}  and ss.class_id =#{classId} and ss.submit_status = 1 " +
            "GROUP BY qi.question_id,qi.question_num,question_type_name," +
            "qi.question_bank_id,qi.question_score, qi.question_alias ORDER BY scoreRate ASC;\n")
    List<Map<String, Object>> selectQuestionStatsByExamDetailIdAndClassDetailId(
            @Param("examDetailId") Long examDetailId, @Param("classId") Long classId);

    @Select("SELECT " +
            "qi.question_id as questionId," +
            "qi.question_num as questionNum, qi.question_alias as questionAlias, qi.question_bank_id as questionBankId, " +
            "qi.question_type_name as questionTypeName, qs.score as score,  qi.question_score as questionScore " +
            "FROM t_question_score qs JOIN t_question_info qi ON qs.question_id = qi.question_id " +
            "JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "WHERE ss.exam_detail_id = #{examDetailId}  and ss.class_id =#{classId} and ss.submit_status = 1 " +
            "ORDER BY questionScore ASC;\n")
    List<Map<String, Object>> selectQuestionScoreByExamDetailIdAndClassDetailId(
            @Param("examDetailId") Long examDetailId, @Param("classId") Long classId);

    @Select("SELECT " +
            "qi.question_id as questionId, qi.is_subjective_item as isSubjectiveItem, " +
            "qi.question_num as questionNum, qi.question_bank_id as questionBankId, ss.id as submitId, " +
            "qi.question_type_name as questionTypeName, qs.score as score,  qi.question_score as questionScore " +
            "FROM t_question_score qs JOIN t_question_info qi ON qs.question_id = qi.question_id " +
            "JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "WHERE ss.exam_detail_id = #{examDetailId} and ss.submit_status = 1 " +
            "ORDER BY questionScore ASC;\n")
    List<Map<String, Object>> selectQuestionScoreByExamDetail(
            @Param("examDetailId") Long examDetailId);

    @Select("SELECT * FROM t_question_score WHERE student_submit_id = #{studentSubmitId}")
    List<QuestionScore> selectBySubmitId(@Param("studentSubmitId") Long studentSubmitId);

    @Select("SELECT * FROM t_question_score WHERE question_id = #{questionId}")
    List<QuestionScore> selectByQuestionId(@Param("questionId") Long questionId);

    @Select("<script> SELECT qs.score, ss.student_name as studentName, ss.student_num as studentNum, " +
            "ss.score as totalScore, ss.id as submitId, qs.student_answer as studentAnswer  " +
            ", ci.class_id as classInfoId, ci.grade_info_id as gradeInfoId , qs.question_id questionId " +
            "FROM t_question_score qs " +
            "LEFT JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "LEFT JOIN t_class_info ci on ss.class_info_id = ci.id " +
            "WHERE ss.submit_status = 1 and qs.question_id in <foreach collection='ids' item='id' open='(' separator=',' close=')'> #{id} </foreach> </script>")
    List<Map<String, Object>> selectScoresWithSubmitsByQuestionIds(@Param("ids") List<Long> questionIds);

    @Select("SELECT qs.score, ss.student_name as studentName, ss.student_num as studentNum, " +
            "ss.score as totalScore, ss.id as submitId, ss.paper_images paperImages, qs.student_answer as studentAnswer  " +
            ", ci.class_id as classInfoId, ci.grade_info_id as gradeInfoId " +
            "FROM t_question_score qs " +
            "LEFT JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "LEFT JOIN t_class_info ci on ss.class_info_id = ci.id " +
            "WHERE qs.question_id = #{questionId} and ss.submit_status = 1 ")
    List<Map<String, Object>> selectScoresWithSubmitsByQuestionId(@Param("questionId") Long questionId);

    @Select("<script>" +
            "SELECT tqs.* FROM t_question_score tqs left join t_student_submit tss on tqs.student_submit_id =tss.id" +
            " WHERE tss.exam_detail_id = #{examDetailId} and tss.submit_status = 1 and  tqs.question_id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    List<QuestionScore> selectByExamDetailIdAndQuestionIds(@Param("examDetailId") Long examDetailId, @Param("ids") List<Long> questionIds);

    @Select("SELECT qs.question_id as questionId, qs.score as score, ss.class_id as classId, " +
            " CASE WHEN qs.score = 0 THEN 0 WHEN qs.score = qi.question_score THEN 1 ELSE 2 END AS isRight, " +
            "ss.student_name as studentName, qi.question_score as questionScore FROM t_question_score qs LEFT JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "LEFT JOIN t_question_info qi ON qi.question_id = qs.question_id WHERE ss.exam_detail_id =#{examId} and ss.submit_status = 1 and qi.exam_detail_id = #{examId} ")
    List<Map<String, Object>> selectQuestionAnswerList(Long examId);

    @Select("SELECT qs.question_id as questionId, qs.score as score," +
            " CASE WHEN qs.score = 0 THEN 0 WHEN qs.score = qi.question_score THEN 1 ELSE 2 END AS isRight, " +
            "ss.student_name as studentName, qi.question_score as questionScore FROM t_question_score qs LEFT JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "LEFT JOIN t_question_info qi ON qi.question_id = qs.question_id WHERE ss.exam_detail_id =#{examId} and ss.student_id =#{studentId}")
    List<Map<String, Object>> selectStudentQuestionAnswerList(@Param("examId") Long id, @Param("studentId") String studentId);

    @Select("SELECT " +
            "qi.question_id as questionId," +
            "qi.question_num as questionNum, qi.question_bank_id as questionBankId, " +
            "qi.question_type_name as questionTypeName, ROUND(AVG(qs.score * 100)/ qi.question_score, 2) as scoreRate " +
            "FROM t_question_score qs JOIN t_question_info qi ON qs.question_id = qi.question_id " +
            "JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "WHERE ss.exam_detail_id = #{examId} and ss.student_id =#{studentId} " +
            "GROUP BY qi.question_id,qi.question_num,question_type_name," +
            "qi.question_bank_id,qi.question_score ORDER BY scoreRate ASC;\n")
    List<Map<String, Object>> selectStudentQuestionStats(@Param("examId") Long id, @Param("studentId") String studentId);

    /**
     * 根据考试ID和学生ID查询题目得分
     */
    @Select("SELECT qs.* FROM t_question_score qs " +
            "INNER JOIN t_student_submit ss ON qs.student_submit_id = ss.id " +
            "WHERE ss.exam_detail_id = #{examId} AND ss.student_id = #{studentId}")
    List<QuestionScore> selectByExamAndStudent(@Param("examId") Long examId, @Param("studentId") String studentId);

    List<QuestionScore> selectQuestionStatsByExamDetailIds(List<Long> examDetailIds);

    /**
     * 根据作业详情查询学生的错题()
     * @param examDetailId 作业详情id
     * @return
     */
    List<QuestionScore> selectQuestionByExamDetailId(Long examDetailId);


    void deleteByExamDetailId(Long id);

    @Select("<script>" +
            "SELECT * FROM t_question_score " +
            " WHERE  student_submit_id IN " +
            "<foreach collection='studentSubmitIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    List<QuestionScore> selectListBySubmitIds(List<Long> studentSubmitIds);

    List<QuestionScore> selecSubmitByExamDetail(Long examDetailId);

    @Select("select * from t_question_score where exam_detail_id = #{examDetailId} and student_submit_id = #{studentSubmitId}")
    List<QuestionScore> getStudentQuestionScore(@Param("studentSubmitId") Long studentSubmitId, @Param("examDetailId") Long examDetailId);

}
